//
//  InitDbTableExamOnLine.m
//  NursingManages
//
//  Created by Zhen Huang on 17/9/26.
//  Copyright © 2017年 huangzhen. All rights reserved.
//

#import "InitDbTableExamOnLine.h"
#import "DBHelper.h"
#import "Model_Hr_ExamAnswer.h"
#import "Networking.h"
#import <TMCache.h>
#import "Model_Hr_Question_Item.h"
@implementation InitDbTableExamOnLine


- (instancetype)init{
    
    NSString *url = AppServiceConfig.GetServiceUrl;
    self.uploadUrl  = [NSString stringWithFormat:@"%@/%@?token=%@",url,@"UploadExamResult",[[TMCache sharedCache] objectForKey:@"deviceToken"]];
    
    return self;
    
}



-(NSString*) GetMainTableSql{
    
    
    NSString *sql = @"create table if not exists hr_exam_result\
    (\
    res_id               text not null,\
    exp_id               text not null,\
    emp_id               text not null,\
    emp_code             text,\
    emp_name             text,\
    dept_id              text,\
    dept_name            text,\
    sub_time             text,\
    score                numeric,\
    result               text,\
    start_time           text,\
    usetimes             text,\
    markup_flag          numeric,\
    markup_score         numeric,\
    hasmarkup            numeric,\
    ips                  text,\
    org_id               text,\
    temp_flag            numeric\
    )";
    
    
    
    return sql;
}
-(NSString*) GetDetailTableSql{
    
    
    //'ques_id'text,'title'text,'typeid'text,'bankid'text,'answer'text,'caseid'text,'option1'text,'option2'text,'option3'text,'option4'text,'option5'text,'option6'text,'option7'text,'option8'text,'option9'text,'option10'text,'order_num'text,'thoughts'text,'val'text,'dif_level'text,'answer_flag'text
    
    return @"create table if not exists hr_exam_detail\
    (\
    res_id           text not null,\
    ques_id          text not null,\
    res_answer       text,\
    right_flag       numeric,\
    ques_score       numeric,\
    answer_flag      numeric,\
    typeid           text,\
    title            text,\
    bankid           text,\
    answer           text,\
    option1          text,\
    option2          text,\
    option3          text,\
    option4          text,\
    option5          text,\
    option6          text,\
    option7          text,\
    option8          text,\
    option9          text,\
    option10         text,\
    order_num        text,\
    thoughts         text\
    )";
}


-(instancetype)primary:(Model_Hr_Exam_Primary *)inspectModel
{
    NSString *string =   [self GetInspectSql:inspectModel];
//    NSString *derailString =   [self GetInspectDetailSql:detailModel];
    
    @try {
        
        [DBHelper.DB open];
        
        [DBHelper.DB beginTransaction];
        
        [DBHelper.DB executeUpdate:string];
        
        
        [DBHelper.DB commit];
        
        
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发现错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
    
    
    
    return self;
}


- (instancetype)inspectDetailModel:(Model_Hr_ExamAnswer *)detailModel {
    
    NSString *derailString =   [self GetInspectDetailSql:detailModel];
    
    @try {
        
        [DBHelper.DB open];
        
        [DBHelper.DB beginTransaction];
        
//        [DBHelper.DB executeUpdate:string];
        
        [DBHelper.DB executeUpdate:derailString];
        
        [DBHelper.DB commit];
        
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发现错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
    
    
    return self;

}


-(NSString*) GetInspectSql:(Model_Hr_Exam_Primary*)model{
    
    NSString *sql1 = @"insert into hr_exam_result(";
    NSString *sql2 = @" values(";
    
    
    sql1 = [sql1 stringByAppendingString:@"res_id,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.RES_ID]];
    
    sql1 = [sql1 stringByAppendingString:@"exp_id,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.EXP_ID]];
    
    sql1 = [sql1 stringByAppendingString:@"emp_id,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.EMP_ID]];
    
    sql1 = [sql1 stringByAppendingString:@"emp_code,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.EMP_CODE]];
    
    sql1 = [sql1 stringByAppendingString:@"emp_name,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.EMP_NAME]];
    
    sql1 = [sql1 stringByAppendingString:@"dept_id,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.DEPT_ID]];
    
    sql1 = [sql1 stringByAppendingString:@"dept_name,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.DEPT_NAME]];
    
    sql1 = [sql1 stringByAppendingString:@"sub_time,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.SUB_TIME]];
    
//    sql1 = [sql1 stringByAppendingString:@"score,"];
//    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%.2f',",model.SCORE]];
    
    sql1 = [sql1 stringByAppendingString:@"result,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.RESULT]];
    
    
    sql1 = [sql1 stringByAppendingString:@"start_time,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.START_TIME]];
    
    sql1 = [sql1 stringByAppendingString:@"usetimes,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.USETIMES]];
    
    sql1 = [sql1 stringByAppendingString:@"score,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%.2f',",[model.SCORE floatValue]]];
    
    
    sql1 = [sql1 stringByAppendingString:@"markup_flag,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%ld',",model.MARKUP_FLAG]];
    
    sql1 = [sql1 stringByAppendingString:@"markup_score,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%ld',",model.MARKUP_SCORE]];
    
    sql1 = [sql1 stringByAppendingString:@"hasmarkup,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%ld',",model.HASMARKUP]];
    
    sql1 = [sql1 stringByAppendingString:@"ips,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.IPS]];
    
    sql1 = [sql1 stringByAppendingString:@"org_id,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.ORG_ID]];
    
    sql1 = [sql1 stringByAppendingString:@"score,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.SCORE]];
    
    sql1 = [sql1 stringByAppendingString:@"title,"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.title]];
    

    sql1 = [sql1 stringByAppendingString:@"temp_flag"];
    sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%ld'",model.TEMP_FLAG]];
    
    
    
    sql1 = [sql1 stringByAppendingString:@")"];
    sql2 = [sql2 stringByAppendingString:@")"];
    
    NSString *sql3 = [sql1 stringByAppendingString:sql2];
    
    NSLog(@"%@",sql3);
    return sql3;
}

-(NSString*) GetInspectDetailSql:(Model_Hr_ExamAnswer*)model{
    

    NSString *sql = [NSString stringWithFormat:@"update hr_exam_detail set res_answer = '%@', right_flag = '%ld',ques_score = '%ld' where  ques_id = '%@' AND res_id = '%@'",model.RES_ANSWER,model.RIGHT_FLAG,model.QUES_SCORE,model.QUES_ID,model.RES_ID];

//        NSString *sql1 = @"insert into hr_exam_detail(";
//        NSString *sql2 = @" values(";
//        
//        sql1 = [sql1 stringByAppendingString:@"res_id,"];
//        sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.RES_ID]];
//        
//        sql1 = [sql1 stringByAppendingString:@"ques_id,"];
//        sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.QUES_ID]];
//        
//        sql1 = [sql1 stringByAppendingString:@"res_answer,"];
//        sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@',",model.RES_ANSWER]];
//        
//        sql1 = [sql1 stringByAppendingString:@"right_flag,"];
//        sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%ld',",model.RIGHT_FLAG]];
//    
//        sql1 = [sql1 stringByAppendingString:@"ques_score,"];
//        sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%ld',",model.QUES_SCORE]];
//    
//        sql1 = [sql1 stringByAppendingString:@"typeid"];
//        sql2 = [sql2 stringByAppendingString:[NSString stringWithFormat:@"'%@'",model.TYPEID]];
//        
//        
//        sql1 = [sql1 stringByAppendingString:@")"];
//        sql2 = [sql2 stringByAppendingString:@")"];
//        NSString *sql3 = [sql1 stringByAppendingString:sql2];
//        NSLog(@"detail%@",sql3);
    

        return sql;
    
}

- (void)deleteTableWithHSaTableCode:(NSString *)ques_id
{
    [DBHelper.DB open];
    NSString *sql = [NSString stringWithFormat:@"update hr_exam_detail set res_answer = null, right_flag = null,ques_score = null where  ques_id = '%@'",ques_id];
    
//    NSString *sql1 = [NSString stringWithFormat:@"update hr_exam_detail set res_answer = null, right_flag = null,ques_score = null where  ques_id = '%@'",ques_id];

    BOOL result = [DBHelper.DB executeUpdate:sql];
    
//    BOOL result1 = [DBHelper.DB executeUpdate:sql1];

    NSLog(result ? @"修改成功":@"修改失败");
//    NSLog(result1 ? @"修改成功":@"修改失败");

    [DBHelper.DB close];
    
}

-(NSString * )getScoreWithResid:(NSString *)res_id{
    
    
    [DBHelper.DB open];
    
    NSString * score = [DBHelper.DB stringForQuery:@"select sum (ques_score) from hr_exam_detail where res_id = '%@' AND right_flag = '1'",res_id];
    
    [DBHelper.DB close];
    
    return score;

}

- (NSArray *)getDetailWithRight:(NSString *)res_id
{
    [DBHelper.DB open];
    
    NSString* sql = [NSString stringWithFormat:@"select * from hr_exam_detail where res_id = '%@' AND right_flag = '1'",res_id];
    NSLog(@"%@",sql);
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self TransalteDetailToModel:set];
    [DBHelper.DB close];
    return array;

}


-(void) startUpload:(NSString *)inspect_primary
     inspect_detail:(NSString *)inspect_detail
              block: (void(^)(NSDictionary *))callback {
    
    
    NSMutableDictionary*dic=[[NSMutableDictionary alloc] init];
    [dic setObject:inspect_primary forKey:@"EXAM_PRIMARY"];
    [dic setObject:inspect_detail forKey:@"EXAM_ANSWER"];
    
    AFHTTPRequestOperationManager *manager = [AFHTTPRequestOperationManager manager];
    
    manager.requestSerializer = [AFHTTPRequestSerializer serializer];
    
    manager.responseSerializer = [AFJSONResponseSerializer serializer];
    
    manager.responseSerializer.acceptableContentTypes= [NSSet setWithObjects:@"application/json", @"text/html",@"text/json",@"text/javascript", nil] ;//如果报接受类型不一致请替换一致text/html或别的
    
    
    [manager POST:self.uploadUrl parameters:dic success:^(AFHTTPRequestOperation *operation, id responseObject) {
        
        NSLog(@"结果%@",responseObject);
        if (callback) {
            callback(responseObject);
        }
        
        
    } failure:^(AFHTTPRequestOperation *operation, NSError *error) {
        
        if (callback) {
            NSDictionary *dict = [[NSDictionary alloc]initWithObjectsAndKeys:@"0",@"flag",error,@"msg", nil];
            callback(dict);
        }
        
    }];
}

-(NSMutableArray *) GetPrimaryUnUploadTableWithResid:(NSString *)res_id

{
    [DBHelper.DB open];
    NSString* sql = [NSString stringWithFormat:@"select * from hr_exam_result where res_id = '%@'",res_id];
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self PrimaryTransalteToModel:set];
    [DBHelper.DB close];
    return array;
    
    
}
-(NSMutableArray *) PrimaryTransalteToModel:(FMResultSet*) set{
    
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        
        Model_Hr_Exam_Primary  *model = [[Model_Hr_Exam_Primary alloc]init];
        model.RES_ID = [set stringForColumn:@"res_id"];
        model.EXP_ID = [set stringForColumn:@"exp_id"];
        model.EMP_ID = [set stringForColumn:@"emp_id"];
        model.EMP_CODE = [set stringForColumn:@"emp_code"];
        model.EMP_NAME = [set stringForColumn:@"emp_name"];
        model.DEPT_ID = [set stringForColumn:@"dept_id"];
        model.DEPT_NAME = [set stringForColumn:@"dept_name"];
        model.SUB_TIME = [set stringForColumn:@"sub_time"];
        model.SCORE = [set stringForColumn:@"score"];
        model.RESULT = [set stringForColumn:@"result"];
        model.START_TIME = [set stringForColumn:@"start_time"];
        model.USETIMES = [set stringForColumn:@"usetimes"];
        model.MARKUP_FLAG = [[set stringForColumn:@"markup_flag"]integerValue];
        model.MARKUP_SCORE = [[set stringForColumn:@"markup_score"]integerValue];
        model.HASMARKUP = [[set stringForColumn:@"hasmarkup"]integerValue];
        model.IPS = [set stringForColumn:@"ips"];
        model.ORG_ID = [set stringForColumn:@"org_id"];
        model.TEMP_FLAG = [[set stringForColumn:@"temp_flag"]integerValue];
        model.title = [set stringForColumn:@"title"];
        [array addObject:model];
        
    }
    
    return array;
}

- (NSMutableArray *)GetDetailIUnUploadTableWithResid:(NSString *)res_id {
    [DBHelper.DB open];
    
    NSString* sql = [NSString stringWithFormat:@"select * from hr_exam_detail where res_id = '%@'",res_id];
    NSLog(@"%@",sql);
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self TransalteDetailToModel:set];
    [DBHelper.DB close];
    return array;

}

-(NSMutableArray *) TransalteDetailToModel:(FMResultSet*) set{
    
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_ExamAnswer  *model = [[Model_Hr_ExamAnswer alloc]init];
        model.RES_ID = [set stringForColumn:@"res_id"];
        model.QUES_ID = [set stringForColumn:@"ques_id"];
        model.RES_ANSWER = [set stringForColumn:@"res_answer"];
        model.RIGHT_FLAG = [[set stringForColumn:@"right_flag"]integerValue];
        model.QUES_SCORE = [[set stringForColumn:@"ques_score"]integerValue];
        model.TYPEID = [set stringForColumn:@"typeid"];
        model.ANSWER_FLAG = [[set stringForColumn:@"answer_flag"]integerValue];
        model.TITLE = [set stringForColumn:@"title"];
        model.ANSWER = [set stringForColumn:@"answer"];
        model.BANKID = [set stringForColumn:@"bankid"];
        model.OPTION1 = [set stringForColumn:@"option1"];
        model.OPTION2 = [set stringForColumn:@"option2"];
        model.OPTION3 = [set stringForColumn:@"option3"];
        model.OPTION4 = [set stringForColumn:@"option4"];
        model.OPTION5 = [set stringForColumn:@"option5"];
        model.OPTION6 = [set stringForColumn:@"option6"];
        model.OPTION7 = [set stringForColumn:@"option7"];
        model.OPTION8 = [set stringForColumn:@"option8"];
        model.OPTION9 = [set stringForColumn:@"option9"];
        model.OPTION10 = [set stringForColumn:@"option10"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.THOUGHTS = [set stringForColumn:@"thoughts"];

    
        [array addObject:model];
        
    }
    
    return array;
}

- (void)deleteTable:(NSString *)res_id
{
    [DBHelper.DB open];
    NSString *sql = [NSString stringWithFormat:@"delete from hr_exam_result where res_id = '%@'",res_id];
    BOOL result = [DBHelper.DB executeUpdate:sql];
    NSLog(result ? @"删除成功":@"删除失败");
    [DBHelper.DB close];

}

- (void)deleteDetail:(NSString *)res_id
{
    [DBHelper.DB open];
    NSString *sql = [NSString stringWithFormat:@"delete from hr_exam_detail where res_id = '%@'",res_id];
    BOOL result = [DBHelper.DB executeUpdate:sql];
    NSLog(result ? @"删除成功":@"删除失败");
    [DBHelper.DB close];
    
}




-(void)deleteDetailArray:(NSArray *)inspectDetailArray {
    
    
    NSArray *array =   [self upDateInspecDetail_new:inspectDetailArray];
    
    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        
        
        for (NSString  * sql  in array) {
            
            [DBHelper.DB executeUpdate:sql];
        }
        
        [DBHelper.DB commit];
        
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发现错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
    
    
}


- (NSMutableArray  *)upDateInspecDetail_new:(NSArray  *)array
{
    NSMutableArray *arrSql = [[NSMutableArray alloc]init];
    
    for (Model_Hr_Question_Item *model in array) {
        
    NSString *sql = [NSString stringWithFormat:@"update hr_exam_questions set answers = null where answers is not null AND ques_id = '%@'",model.QUES_ID];

        [arrSql addObject:sql];
    }
    
    return arrSql;
    
}


- (NSMutableArray *)getALLPrimary
{
    
    [DBHelper.DB open];
    NSString* sql = [NSString stringWithFormat:@"select * from hr_exam_result"];
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self PrimaryTransalteToModel:set];
    [DBHelper.DB close];
    return array;
    
 
}

- (void)insetInDetail:(NSArray *)array

{
    
    @try {
        
        [DBHelper.DB open];
        [DBHelper.DB beginTransaction];
        

        for (Model_Hr_ExamAnswer * model in array) {
        [DBHelper.DB executeUpdate:@"insert into hr_exam_detail(res_id,ques_id,res_answer,right_flag,ques_score,typeid,answer_flag,title,bankid,answer,option1,option2,option3,option4,option5,option6,option7,option8,option9,option10,order_num,thoughts)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",model.RES_ID,model.QUES_ID,model.RES_ANSWER,model.RIGHT_FLAG,model.QUES_SCORE,model.TYPEID,model.ANSWER_FLAG,model.TITLE,model.BANKID,model.ANSWER,model.OPTION1,model.OPTION2,model.OPTION3,model.OPTION4,model.OPTION5,model.OPTION6,model.OPTION7,model.OPTION8,model.OPTION9,model.OPTION10,model.ORDER_NUM,model.THOUGHTS];
        }
        
        [DBHelper.DB commit];
        
    } @catch (NSException *exception) {
        
        [DBHelper.DB rollback];
        NSLog(@"发生错误，数据已回滚：%@",exception);
        
    } @finally {
        [DBHelper.DB close];
    }
    

}



//- (NSMutableArray  *)InsertInDetail:(NSArray  *)array
//{
//    
//    NSMutableArray *arrSql = [[NSMutableArray alloc]init];
//    
//    for (Model_Hr_ExamAnswer * model in array) {
//        
////    NSString *sql = [NSString stringWithFormat:@"insert into hr_exam_detail(res_id,ques_id,res_answer,right_flag,ques_score,typeid)values (?,?,?,?,?,?)",model.RES_ID,model.QUES_ID,model.RES_ANSWER,model.RIGHT_FLAG,model.QUES_SCORE,model.TYPEID];
//
////        [arrSql addObject:sql];
////        // NSLog(@"正在写入第%lu条记录", 1 + (unsigned long)[records indexOfObject:model]);
//        [DBHelper.DB executeUpdate:@"insert into pub_employee(res_id,ques_id,res_answer,right_flag,ques_score,typeid)values (?,?,?,?,?,?)",model.RES_ID,model.QUES_ID,model.RES_ANSWER,model.RIGHT_FLAG,model.QUES_SCORE,model.TYPEID];
//    }
//    
//    return arrSql;
//    
//}

- (NSMutableArray *)unAnswerQuesWithRes_id:(NSString *)res_id{
    
    
    [DBHelper.DB open];
    
    NSString* sql = [NSString stringWithFormat:@"select * from hr_exam_detail where res_answer IS NULL AND res_id = '%@'",res_id];
    
    NSLog(@"sql = %@",sql);
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self TransalteDetailToModel:set];
    [DBHelper.DB close];
    return array;
    

}


@end
